package com.rbn.stockmobiledevices.utils;

import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.DatabaseSequenceFilter;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.FilteredDataSet;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.dataset.xml.FlatXmlWriter;
import org.dbunit.operation.DatabaseOperation;

public class DBUtils

{

	public void exportDbData(String driverName, String urlDB,
			String userDB, String passwordDB, String schemaBD, String nameXML, OutputStream out)
			throws SQLException {

		Connection conn = null;

		try {
			// Connect to the database
			DriverManager.registerDriver((Driver) Class.forName(driverName).newInstance());
			conn = DriverManager.getConnection(urlDB, userDB, passwordDB);
			IDatabaseConnection connection = new DatabaseConnection(conn, schemaBD);

			DatabaseSequenceFilter filter = new DatabaseSequenceFilter(connection);
			IDataSet datasetAll = new FilteredDataSet(filter, connection.createDataSet());
			QueryDataSet partialDataSet = new QueryDataSet(connection);

			String[] listTableNames = filter.getTableNames(datasetAll);
			for (int i = 0; i < listTableNames.length; i++) {
				final String tableName = listTableNames[i];
				// Specify the SQL to run to retrieve the data
				partialDataSet.addTable(tableName);
			}

			// Specify the location of the flat file(XML)
			FlatXmlWriter datasetWriter = new FlatXmlWriter(out);
			// new FileOutputStream("C:\\" + nameXML + ".xml")
			
			// Export the data
			datasetWriter.write(partialDataSet);
			
		} catch (Exception exc) {
			exc.printStackTrace();

		} finally {
			conn.close();
		}
	}

	public void exportDbDataPartial(String driverName, String urlDB, String userDB, String passwordDB, String schemaBD, String nameXML, OutputStream out) throws SQLException {
		Connection conn = null;

		try {
			// Connect to the database
			DriverManager.registerDriver((Driver) Class.forName(driverName).newInstance());
			conn = DriverManager.getConnection(urlDB, userDB, passwordDB);
			IDatabaseConnection connection = new DatabaseConnection(conn,schemaBD);

			QueryDataSet partialDataSet = new QueryDataSet(connection);
			// Specify the SQL to run to retrieve the data
			partialDataSet.addTable("web_direccion");
			partialDataSet.addTable("web_usuario");

			// Specify the location of the flat file(XML)
			FlatXmlWriter datasetWriter = new FlatXmlWriter(out);

			// Export the data
			datasetWriter.write(partialDataSet);

		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			conn.close();
		}
	}

	public void importDbData(String driverName, String urlDB, String userDB, String passworDB, String nameXML, InputStream in) throws SQLException {

		Connection conn = null;
		try {
			// Connect to the database
			DriverManager.registerDriver((Driver) Class.forName(driverName)
					.newInstance());
			conn = DriverManager.getConnection(urlDB, userDB, passworDB);
			IDatabaseConnection connection = new DatabaseConnection(conn);

			FlatXmlDataSetBuilder builder = new FlatXmlDataSetBuilder();
			builder.setColumnSensing(true);
			IDataSet dataSet = builder.build(in);
			
			DatabaseOperation.INSERT.execute(connection, dataSet);

		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			conn.close();
		}
	}

	public void deleteData(String driverName, String urlDB,
			String userDB, String passworDB, String nameXML, InputStream in)
			throws SQLException {
		Connection conn = null;
		try {
			// Connect to the database
			DriverManager.registerDriver((Driver) Class.forName(driverName).newInstance());
			conn = DriverManager.getConnection(urlDB, userDB, passworDB);
			IDatabaseConnection connection = new DatabaseConnection(conn);
			
			FlatXmlDataSetBuilder builder = new FlatXmlDataSetBuilder();
			builder.setColumnSensing(true);
			IDataSet dataSet = builder.build(in);
			
			DatabaseOperation.DELETE.execute(connection, dataSet);

		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			conn.close();
		}
	}

}
